package com.portal.dao;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;

import org.apache.log4j.Logger;
import utiles.AppProperties;
import utiles.ConexionPool;

import com.portal.dto.AlertaDTO;
import com.portal.vo.AlertaVO;

public class AlertaDAO {
	private static Logger log = Logger.getLogger(AlertaDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	
	public List<AlertaDTO> setGrabaAlerta(AlertaVO alerta)throws SQLException,IOException{
		
		Connection conn = ConexionPool.getConnectionPool();
		List<AlertaDTO> lista = new ArrayList<AlertaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_ALERTA.setGrabaAlerta(?,?,?,?,?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);	
			stmt.setString(1, alerta.getIdAlerta());
			stmt.setString(2, alerta.getIdUsuario());
			stmt.setString(3, alerta.getIdPaciente());
			stmt.setString(4, alerta.getMensaje());
			stmt.setString(5, alerta.getActivo());
			stmt.setString(6, alerta.getIdUsuFin());
			stmt.setString(7, alerta.getThSM());
			stmt.setString(8, alerta.getFichaClinica());
			stmt.setString(9, alerta.getThEEG());
			stmt.registerOutParameter(10, OracleTypes.VARCHAR);					
			stmt.execute();
			AlertaDTO ale = new AlertaDTO(); 
			ale.setCreaGraba(stmt.getString(10));
			lista.add(ale);		 
			stmt.close();			
		} catch (Exception ex) {
			log.error("Error AlertaDAO.setGrabaAlerta SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());			
		} finally{
			conn.close();
			conn = null;
		}
		return lista;
	}
	
	public List<AlertaDTO> getAlertas(String idPaciente) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();
		List <AlertaDTO> lista = new ArrayList<AlertaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_ALERTA.getAlertas(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idPaciente);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				AlertaDTO alerDTO = new AlertaDTO();				
				alerDTO.setIdAlerta(rs.getString(1));
				alerDTO.setFecDigitacion(rs.getString(2));
				alerDTO.setActivo(rs.getString(3));
				alerDTO.setUsuario(rs.getString(4));
				alerDTO.setMensaje(rs.getString(5));
				lista.add(alerDTO);	
			}
		} catch (Exception ex) {
			log.error("Error AlertaDAO.getAlertas SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());			
		} finally{
			conn.close();
			conn = null;			
		}
		return lista;
	}
	
	public List<AlertaDTO> getDetalle(String idAlerta)throws SQLException,IOException{
		
		Connection conn = ConexionPool.getConnectionPool();
		List<AlertaDTO> lista = new ArrayList<AlertaDTO>();
		
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_ALERTA.getDetalle(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idAlerta);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				AlertaDTO alerDTO = new AlertaDTO();
				alerDTO.setUsuario(rs.getString(1));
				alerDTO.setActivo(rs.getString(2));
				alerDTO.setMensaje(rs.getString(3));
				alerDTO.setThSM(rs.getString(4));
				alerDTO.setFichaClinica(rs.getString(5));
				alerDTO.setThEEG(rs.getString(6));
				alerDTO.setFecDigitacion(rs.getString(7));
				alerDTO.setFecFinalizacion(rs.getString(8));
				alerDTO.setUsuarioFin(rs.getString(9));			
				lista.add(alerDTO);		
			}
		} catch (Exception ex) {
			log.error("Error AlertaDAO.getDetalle SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());	
		} finally{
			conn.close();
			conn = null;
		}
		return lista;
	}
	
	public List<AlertaDTO> getAlertasByIDPAC(String idPac, String origen) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();
		List<AlertaDTO> lista = new ArrayList<AlertaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_ALERTA.getAlertasByPac(?,?,?) }";
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idPac);
			stmt.setString(2, origen);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while(rs.next()){
				AlertaDTO alerDTO = new AlertaDTO();
				alerDTO.setMensaje(rs.getString(1));
				alerDTO.setFecDigitacion(rs.getString(2));			
				lista.add(alerDTO);
			}
		} catch (Exception ex) {
			log.error("Error AlertaDAO.getAlertasByIDPAC SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return lista;
	}
	
	public List<AlertaDTO> setDesAlerta(String idAlerta)throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();
		List<AlertaDTO> lista = new ArrayList<AlertaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_ALERTA.setDesactivaAlerta(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idAlerta);
			stmt.registerOutParameter(2, OracleTypes.NUMBER);					
			stmt.execute();
			AlertaDTO ale = new AlertaDTO(); 
			ale.setIdAlerta(stmt.getString(2));
			lista.add(ale);		 
			stmt.close();
		} catch (Exception ex) {
			log.error("Error AlertaDAO.setDesAlerta SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return lista;
	}
}
